package com.ls.mysql;

import com.ls.mysql.dao.SurplusDao;
import com.ls.mysql.dao.UserDao;
import com.ls.mysql.entity.YzUsers;
import com.ls.mysql.other.Thread1;
import com.ls.mysql.util.EncryptUtil;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author Maple.Chen
 * @date 2020年3月20日
 * @description <p>单元测试</p>
 */
@RunWith(SpringRunner.class)
@SpringBootTest
@Transactional
public class Excel_test {

    // import Excel方法用到的常量
    private final static String excel2003L = ".xls"; // 2003- 版本的excel
    private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel

    @Autowired
    private UserDao ud;

    @Autowired
    private SurplusDao spd;

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Test
    public void MD5() {
//		System.out.println("一次" + EncryptUtil.getMD5String("123456"));
//		System.out.println("二次" + EncryptUtil.getMD5String(EncryptUtil.getMD5String("123456")));
    }

    //    @Test
    @Rollback(false)//false:关闭单元测试的保护机制，对数据库进行实际增删改操作
    public void batchInsert_Excel_Import() throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, InterruptedException {
        long startTime = System.currentTimeMillis();
        List<YzUsers> list = Import(new File("G:" + File.separator + "test" + File.separator + "yz_user_Excel_test.xls"));
        /**
         * 每个线程插入的数据为1000 <--- 最优效率
         * 根据数据记录数获得线程总数，但是并发只有10个线程
         * 根据线程总数对数据进行切割
         */
        int mod_ = 1000;
        int Thread_size = (list.size() % mod_ == 0) ? (list.size() / mod_) : ((list.size() / mod_) + 1);
        List<List<YzUsers>> lis = new ArrayList<>(Thread_size);
        for (int i = 0; i < Thread_size; i++) { //[a,b) [0,1000)
            if (i == (Thread_size - 1)) {
                if (list.size() % mod_ > 0) {
                    lis.add(list.subList(i * mod_, i * mod_ + list.size() % 1000));
                } else {
                    lis.add(list.subList(i * mod_, i * mod_ + mod_));
                }
            } else {
                lis.add(list.subList(i * mod_, i * mod_ + mod_));
            }
        }
        long endTime = System.currentTimeMillis();
        System.out.println("解析Excel所耗费的时间：" + (endTime - startTime) + "ms");
        System.out.println(lis.size() + "/" + Thread_size);
//		Reflect(lis.get(lis.size()-1).get(lis.get(lis.size()-1).size()-1));  //用反射方法代替toString()  遍历某个实体对象所有属性
//		ExecutorService executor = Executors.newFixedThreadPool(Thread_size);
        Thread[] ts = new Thread[Thread_size];
        long startTime2 = System.currentTimeMillis();
        for (int i = 0; i < Thread_size; i++) {
            ts[i] = new Thread(new Thread1(lis.get(i), ud, spd, sqlSessionFactory));
            ts[i].start();
            ts[i].join();
//			Thread1 th=new Thread1(lis.get(i),ud,spd,sqlSessionFactory);
//			executor.execute(th);
        }
//		while(!executor.isTerminated()){
//			if(executor.isTerminated())
//				break;
//		}
//		executor.shutdown();
        long endTime2 = System.currentTimeMillis();
        System.out.println("执行Thread所耗费的时间：" + (endTime2 - startTime2) + "ms");
    }

    //    @Test
    public void Excel_Export() {
        long startTime1 = System.currentTimeMillis();
        List<YzUsers> list = new ArrayList<>();
        YzUsers yu;
        for (int i = 0; i < 10000; i++) {
            yu = new YzUsers();
            yu.setId("u" + Integer.toString(i + 1));
            yu.setRealname("张" + Integer.toString(i));
            yu.setUsername("zhangsan" + Integer.toString(i));
            // 使用MD5一次加密后的串存入数据库
            yu.setPassword(EncryptUtil.getMD5String("123456"));
            // 允许为空的部分
            yu.setQq("qq_" + Integer.toString(i + 1));
            yu.setEmail("email_" + Integer.toString(i + 1));
            yu.setWechat("WeChat_" + Integer.toString(i + 1));
            yu.setMobile("mobile_" + Integer.toString(i + 1));
            yu.setAddress("address_" + Integer.toString(i + 1));
            // 年龄 随机数[16,35)
            yu.setAge((int) Math.random() * (35 - 16) + 16);
            yu.setSex("男");
            yu.setDerive("Earth");
            yu.setHobby("this.爱好");
            yu.setU_img("https://www.baidu.com/");
            list.add(yu);
        }
        String Storage_path = "G:" + File.separator + "test" + File.separator;
        boolean result = Export(list, Storage_path, "yz_user_Excel_test");
        if (result) {
            long endTime1 = System.currentTimeMillis();
            System.out.println("生成Excel所耗费的时间：" + (endTime1 - startTime1) + "ms");
        }
    }

    // 将Test方法生成的测试数据输出至Excel文件
    public static boolean Export(List<YzUsers> u_list, String Storage_path, String FileName) {
        boolean exec_result = false;
        // 创建一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        /**
         * 下面先查看总数据数，每张sheet仅能存储65535行数据
         */
        int sheet_size = (u_list.size() % 65535 == 0) ? (u_list.size() / 65535) : ((u_list.size() / 65535) + 1);
        List<List<YzUsers>> lis = new ArrayList<>(sheet_size);
        int index = 0;
        for (int i = 0; i < sheet_size; i++) {
            if (i == (sheet_size - 1)) {
                lis.add(u_list.subList(index, index + u_list.size() % 65535));
            } else {
                lis.add(u_list.subList(index, index + 65535));
            }
            index += 65535;
        }
        for (int page = 0; page < sheet_size; page++) {
            // 创建一个工作表
            HSSFSheet sheet = workbook.createSheet("sheet" + Integer.toString(page + 1));
            // 添加表头行
            HSSFRow hssfRow = sheet.createRow(0);
            // 设置单元格格式居中
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);

            // 添加表头内容
            HSSFCell headCell = hssfRow.createCell(0);
            headCell.setCellValue("id");
            headCell.setCellStyle(cellStyle);

            headCell = hssfRow.createCell(1);
            headCell.setCellValue("真实姓名");
            headCell.setCellStyle(cellStyle);

            headCell = hssfRow.createCell(2);
            headCell.setCellValue("账号");
            headCell.setCellStyle(cellStyle);

            headCell = hssfRow.createCell(3);
            headCell.setCellValue("密码");
            headCell.setCellStyle(cellStyle);
            // 允许为空部分
            headCell = hssfRow.createCell(4);
            headCell.setCellValue("QQ");
            headCell.setCellStyle(cellStyle);

            headCell = hssfRow.createCell(5);
            headCell.setCellValue("Email");
            headCell.setCellStyle(cellStyle);

            headCell = hssfRow.createCell(6);
            headCell.setCellValue("WeChat");
            headCell.setCellStyle(cellStyle);

            headCell = hssfRow.createCell(7);
            headCell.setCellValue("Mobile");
            headCell.setCellStyle(cellStyle);

            headCell = hssfRow.createCell(8);
            headCell.setCellValue("Address");
            headCell.setCellStyle(cellStyle);

            headCell = hssfRow.createCell(9);
            headCell.setCellValue("Age");
            headCell.setCellStyle(cellStyle);

            headCell = hssfRow.createCell(10);
            headCell.setCellValue("Sex");
            headCell.setCellStyle(cellStyle);

            headCell = hssfRow.createCell(11);
            headCell.setCellValue("籍贯");
            headCell.setCellStyle(cellStyle);

            headCell = hssfRow.createCell(12);
            headCell.setCellValue("爱好");
            headCell.setCellStyle(cellStyle);

            headCell = hssfRow.createCell(13);
            headCell.setCellValue("头像图");
            headCell.setCellStyle(cellStyle);

            // 添加数据内容
            for (int i = 0; i < lis.get(page).size(); i++) {
                hssfRow = sheet.createRow((int) i + 1);
                YzUsers y = (YzUsers) lis.get(page).get(i);

                // 创建单元格，并设置值
                HSSFCell cell = hssfRow.createCell(0);
                cell.setCellValue(y.getId());
                cell.setCellStyle(cellStyle);

                cell = hssfRow.createCell(1);
                cell.setCellValue(y.getRealname());
                cell.setCellStyle(cellStyle);

                cell = hssfRow.createCell(2);
                cell.setCellValue(y.getUsername());
                cell.setCellStyle(cellStyle);

                cell = hssfRow.createCell(3);
                cell.setCellValue(y.getPassword());
                cell.setCellStyle(cellStyle);
                // Others
                cell = hssfRow.createCell(4);
                cell.setCellValue(y.getQq());
                cell.setCellStyle(cellStyle);

                cell = hssfRow.createCell(5);
                cell.setCellValue(y.getEmail());
                cell.setCellStyle(cellStyle);

                cell = hssfRow.createCell(6);
                cell.setCellValue(y.getWechat());
                cell.setCellStyle(cellStyle);

                cell = hssfRow.createCell(7);
                cell.setCellValue(y.getMobile());
                cell.setCellStyle(cellStyle);

                cell = hssfRow.createCell(8);
                cell.setCellValue(y.getAddress());
                cell.setCellStyle(cellStyle);

                cell = hssfRow.createCell(9);
                cell.setCellValue(y.getAge());
                cell.setCellStyle(cellStyle);

                cell = hssfRow.createCell(10);
                cell.setCellValue(y.getSex());
                cell.setCellStyle(cellStyle);

                cell = hssfRow.createCell(11);
                cell.setCellValue(y.getDerive());
                cell.setCellStyle(cellStyle);

                cell = hssfRow.createCell(12);
                cell.setCellValue(y.getHobby());
                cell.setCellStyle(cellStyle);

                cell = hssfRow.createCell(13);
                cell.setCellValue(y.getU_img());
                cell.setCellStyle(cellStyle);

            }
        }
        // 保存Excel文件
        try {
            String Path = Storage_path + FileName + ".xls";
            OutputStream outputStream = new FileOutputStream(Path);
            workbook.write(outputStream);
            outputStream.close();
            exec_result = true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return exec_result;
    }

    // 解析指定格式Excel文件
    public static List<YzUsers> Import(File file) {
        List<YzUsers> list = new ArrayList<>();
        YzUsers yu;
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        Workbook work = null;
        String fileType = file.getName().substring(file.getName().lastIndexOf("."));
        try {
            if (excel2003L.equals(fileType)) {
                work = new HSSFWorkbook(fis); // 2003-
            } else if (excel2007U.equals(fileType)) {
                work = new XSSFWorkbook(fis); // 2007+
            } else {
                // "解析的文件格式有误！"
                return null;
            }
            Sheet sheet = null;
            // 因为Excel不一定只有一张sheet，遍历Excel中所有的sheet
            for (int i = 0; i < work.getNumberOfSheets(); i++) {
                sheet = work.getSheetAt(i);
                if (sheet == null)
                    continue;
                //遍历当前sheet的每一行
                Row row;
                for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
                    row = sheet.getRow(j);
                    // 遍历所有的列
                    yu = new YzUsers();
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        //System.out.print(getCellValue(row.getCell(y))+" ");
                        if (y == 0) yu.setId((String) getCellValue(row.getCell(y)));
                        if (y == 1) yu.setRealname((String) getCellValue(row.getCell(y)));
                        if (y == 2) yu.setUsername((String) getCellValue(row.getCell(y)));
                        if (y == 3) yu.setPassword((String) getCellValue(row.getCell(y)));
                        // Others
                        if (y == 4) yu.setQq((String) getCellValue(row.getCell(y)));
                        if (y == 5) yu.setEmail((String) getCellValue(row.getCell(y)));
                        if (y == 6) yu.setWechat((String) getCellValue(row.getCell(y)));
                        if (y == 7) yu.setMobile((String) getCellValue(row.getCell(y)));
                        if (y == 8) yu.setAddress((String) getCellValue(row.getCell(y)));

                        if (y == 9) yu.setAge(Integer.parseInt((String) getCellValue(row.getCell(y))));
                        if (y == 10) yu.setSex((String) getCellValue(row.getCell(y)));
                        if (y == 11) yu.setDerive((String) getCellValue(row.getCell(y)));
                        if (y == 12) yu.setHobby((String) getCellValue(row.getCell(y)));
                        if (y == 13) yu.setU_img((String) getCellValue(row.getCell(y)));
                    }
                    list.add(yu);
                }
            }
            work.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {
        Object value = null;
        DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
        DecimalFormat df2 = new DecimalFormat("0"); // 格式化数字

        switch (cell.getCellTypeEnum()) {
            case STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    value = sdf.format(cell.getDateCellValue());
                } else {
                    value = df2.format(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }

    /**
     * 方法反射：遍历属性
     */
    public void Reflect(Object model) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        Field[] field = model.getClass().getDeclaredFields();
        for (int j = 0; j < field.length; j++) {     //遍历所有属性
            String name = field[j].getName();    //获取属性的名字
            name = name.substring(0, 1).toUpperCase() + name.substring(1); //将属性的首字符大写，方便构造get，set方法
            String type = field[j].getGenericType().toString();    //获取属性的类型
            if (type.equals("class java.lang.String")) {   //如果type是类类型，则前面包含"class "，后面跟类名
                Method m = model.getClass().getMethod("get" + name);
                String value = (String) m.invoke(model);    //调用getter方法获取属性值
                if (value != null) {
                    System.out.println(name + ".value=" + value);
                }
            }
            if (type.equals("class java.lang.Integer")) {
                Method m = model.getClass().getMethod("get" + name);
                Integer value = (Integer) m.invoke(model);
                if (value != null) {
                    System.out.println(name + ".value=" + value);
                }
            }
            if (type.equals("class java.lang.Short")) {
                Method m = model.getClass().getMethod("get" + name);
                Short value = (Short) m.invoke(model);
                if (value != null) {
                    System.out.println(name + ".value=" + value);
                }
            }
            if (type.equals("class java.lang.Double")) {
                Method m = model.getClass().getMethod("get" + name);
                Double value = (Double) m.invoke(model);
                if (value != null) {
                    System.out.println(name + ".value=" + value);
                }
            }
            if (type.equals("class java.lang.Boolean")) {
                Method m = model.getClass().getMethod("get" + name);
                Boolean value = (Boolean) m.invoke(model);
                if (value != null) {
                    System.out.println(name + ".value=" + value);
                }
            }
            if (type.equals("class java.util.Date")) {
                Method m = model.getClass().getMethod("get" + name);
                Date value = (Date) m.invoke(model);
                if (value != null) {
                    System.out.println(name + ".value=" + value);
                }
            }
        }
    }
}
